// 打开名为deme.db 文件，其中有一张表名为student,有id,name,sex，Chinese,Math.English等列，
// 分别求出男生和女生三门课都及格的人数并输出（用sqlite3的C接口实现）
#include <stdio.h>
#include <sqlite3.h>
#include <stdlib.h>

int main()
{
    sqlite3 *db;
    sqlite3_stmt *stmt;
    const char *db_name = "demo.db";
    const char *sql_query =
        "SELECT sex, COUNT(*) FROM student "
        "WHERE Chinese >= 60 AND Math >= 60 AND English >= 60 "
        "GROUP BY sex;";

    // 打开数据库
    if (sqlite3_open(db_name, &db) != SQLITE_OK)
    {
        fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
        return 1;
    }

    // 准备 SQL 查询语句
    if (sqlite3_prepare_v2(db, sql_query, -1, &stmt, NULL) != SQLITE_OK)
    {
        fprintf(stderr, "Failed to prepare statement: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        return 1;
    }

    printf("三门课都及格的学生人数（按性别）：\n");
    while (sqlite3_step(stmt) == SQLITE_ROW)
    {
        const char *sex = (const char *)sqlite3_column_text(stmt, 0);
        int count = sqlite3_column_int(stmt, 1);
        printf("性别: %s, 人数: %d\n", sex, count);
    }

    // 释放语句句柄和关闭数据库
    sqlite3_finalize(stmt);
    sqlite3_close(db);

    return 0;
}